create  table jms_dm.dm_reback_transfer_plan_sign_sum_dt
(
delivery_network_code            varchar(100) COMMENT '派件网点code' ,
franchisee_code                  varchar(100) COMMENT '加盟商code' ,
agent_code                       varchar(100) COMMENT '代理区code' ,
delivery_network_name            varchar(100) COMMENT '派件网点名字' ,
manage_code                      varchar(100) COMMENT '管理大区code' ,
manage_name                      varchar(100) COMMENT '管理大区名字' ,
agent_name                       varchar(100) COMMENT '代理区名字' ,
franchisee_name                  varchar(100) COMMENT '加盟商名字' ,
 order_source_name               varchar(100) COMMENT '平台名字' ,
order_source_code                varchar(100) COMMENT '平台code' ,
back_sign_cnt                    int(6) COMMENT '应签收量' ,
before_sign_cnt                  int(6) COMMENT '22点前签收量' ,
after_sign_cnt                   int(6) COMMENT '22点后延误签收' ,
zypt_cnt                         int(6) COMMENT '22点到24点签收量' ,
no_sign_cnt                      int(6) COMMENT '未签收量' ,
ypy_cnt                          int(6) COMMENT '一班应签收量' ,
ypz_cnt                          int(6) COMMENT '一班准点签收的量' ,
epy_cnt                          int(6) COMMENT '二班应签收的量' ,
epz_cnt                          int(6) COMMENT '二班准点签收的量' ,
spy_cnt                          int(6) COMMENT '三班应签收的量' ,
spz_cnt                          int(6) COMMENT '三班准点签收的量' ,
date_time                        date COMMENT '业务日期' ,
need_sign_cnt_1030               int(6) COMMENT '10：30应签收' ,
aging_sign_cnt_1030              int(6) COMMENT '10：30准点签收' ,
need_sign_cnt_1130               int(6) COMMENT '11：30应签收' ,
aging_sign_cnt_1130              int(6) COMMENT '11：30准点签收' ,
need_sign_cnt_1400               int(6) COMMENT '14：00应签收' ,
aging_sign_cnt_1400              int(6) COMMENT '14：00准点签收' ,
need_sign_cnt_1800               int(6) COMMENT '18：00应签收' ,
aging_sign_cnt_1800              int(6) COMMENT '18：00准点签收' ,
need_sign_cnt_2200               int(6) COMMENT '22:00应签收量' ,
aging_sign_cnt_2200              int(6) COMMENT '22:00准点签收量' ,
departure_on_time_sum            int(6) COMMENT '准点接件量' ,
departure_later_time_sum         int(6) COMMENT '晚点接件量' ,
arrival_on_time_sum              int(6) COMMENT '准点到车量' ,
arrival_later_time_sum           int(6) COMMENT '晚点到车量' ,
deliver_on_time_sum              int(6) COMMENT '准点派件量' ,
deliver_township_on_time_sum     int(6) COMMENT '乡镇准点派件量' ,
deliver_later_time_sum           int(6) COMMENT '不准点派件量' ,
deliver_township_later_time_sum  int(6) COMMENT '乡镇不准点派件量',
send_need_sign_sum int(6) comment '发件应签收总量'
 )
ENGINE=OLAP
DUPLICATE KEY(delivery_network_code,franchisee_code,agent_code)
comment '退件签收规划签收维度汇总'
PARTITION BY RANGE (date_time) (
   START ("2023-06-12") END ("2023-08-01") EVERY (INTERVAL 1 day))
DISTRIBUTED BY HASH(delivery_network_code) BUCKETS 2
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-365",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "2",
"in_memory" = "false",
"storage_format" = "V2"
);
alter table jms_dm.dm_reback_transfer_plan_sign_sum_dt add column (
      no_appraisal_sum int(6) comment'不考核汇总'
);